package com.F04.dao;

import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import jxl.Sheet;
import jxl.Workbook;

import org.apache.log4j.Logger;

import com.F04.actionForm.F0401ActionForm;
import com.dao.common.DbOperationExt;
import com.util.CommonTools;

public class F0401Dao extends DbOperationExt {

	Logger log = Logger.getLogger(F0401Dao.class);

	public F0401Dao() {

	}

	public F0401Dao(Connection conn) {
		super(conn);
	}

	public List select(F0401ActionForm actionForm) throws SQLException {

		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		Map map = null;
		ResultSet rest = null;

		String materialName = CommonTools.null2String(actionForm
				.getMaterialName());
		String materialCode1 = CommonTools.null2String(actionForm
				.getMaterialCode1());
		String materialCode2 = CommonTools.null2String(actionForm
				.getMaterialCode2());

		sql
				.append("select m.code,m.name,unit_price,quantity from pc_material_cost c,t_materials m");
		sql.append(" where m.code = c.material_code ");
		if (materialCode1 != "") {
			sql.append(" and m.code >= '" + materialCode1 + "'");
		}
		if (materialCode2 != "") {
			sql.append(" and m.code <= '" + materialCode2 + "'");
		}
		if (materialName != "") {
			sql.append(" and m.name like '%" + materialName + "%'");
		}

		log.info(sql.toString());
		rest = super.executeQuery(sql.toString());
		while (rest.next()) {
			map = new HashMap();
			map.put("materialCode", CommonTools.null2String(rest
					.getString("code")));
			map.put("materialName", CommonTools.null2String(rest
					.getString("name")));
			map.put("qty", CommonTools.null2String(rest.getString("quantity")));
			map.put("unitPrice", CommonTools.convertCurrencyRMB(rest.getString("unit_price")));
			list.add(map);
		}
		if (map == null) {
			actionForm.setErrorFlag("2");
			return null;
		}
		return list;
	}

	public boolean upload(F0401ActionForm actionForm){

		String strUrl = actionForm.getStrUrl();
		StringBuffer sql = null;
		StringBuffer sqlDel = new StringBuffer();
		StringBuffer sqlUpdate = new StringBuffer();
		List list = new ArrayList();
		List codeList = new ArrayList();
		Map map = null;
		ResultSet rest = null;
		int i = 0;
		Workbook book = null;
		try {			
			book = Workbook.getWorkbook(new File(strUrl));
			Sheet sheet = book.getSheet(0);
			String code = "";
			String qty = "";
			String price = "";
			
			for (i = 1;i < sheet.getRows() - 1; i++) {
				code = sheet.getCell(1, i).getContents();
				qty = sheet.getCell(6, i).getContents();
				price = sheet.getCell(7, i).getContents();
//				材料数量和材料价格不是数字
				if((!CommonTools.isNumber1(qty))||(!CommonTools.isNumber1(price))){
					actionForm.setUploadError((i+1) + "");
					actionForm.setErrorFlag("1");
					return false;
				}
				
//-------------------------------------------测试start------------------------------------------------------------------------------------				
				if("0".equals(qty)){
					codeList.add(code);
				}				
//-------------------------------------------测试end------------------------------------------------------------------------------------				
				
////				材料的库存是0，价格也是0
//				if("0".equals(qty)){
//					continue;
//				}else if(Float.parseFloat(price)<0){
//					price = String.valueOf(Math.abs(Float.parseFloat(price)));
//					log.info(String.valueOf(i+1));
//					map = new HashMap();
//					map.put("row", String.valueOf(i+1));
//					map.put("code", code);
//					map.put("name", sheet.getCell(2, i).getContents());
//					list.add(map);
//				}
//				sql = new StringBuffer();
//				sql.append("select count(*) qty from pc_material_cost where material_code = '" + code + "'");
//				rest = super.executeQuery(sql.toString());
//				while(rest.next()){
//					sql = new StringBuffer();
////					材料价格表中无该材料的记录
//					if(rest.getInt("qty") == 0){
//						sql.append("insert into pc_material_cost(material_code,unit_price,quantity) values (");
//						sql.append("'" + code + "',substring(convert(varchar,round("+price+",2)),1,charindex('.',convert(varchar,round("+price+",2)))+2),'" + qty + "')");
////					材料价格表中有该材料的记录
//					}else{
//
//						sql.append("update pc_material_cost set unit_price = substring(convert(varchar,round("+price+",2)),1,charindex('.',convert(varchar,round("+price+",2)))+2),");
//						sql.append(" quantity='" + qty + "' ");
//						sql.append(" where material_code = '" + code + "'");
//					}
//				}
//				super.executeUpdate(sql.toString(),false);	
			}
			
//-------------------------------------------测试start------------------------------------------------------------------------------------				
			String strWhere = "material_code not in (";
			String strCode = ",";
			String rtn = ""; 
			Iterator iterator = codeList.iterator();
			while (iterator.hasNext()) {
				rtn = iterator.next().toString();
				strWhere += "'"+rtn+"',";
				strCode += "'"+rtn+"',";
			}
			strWhere = strWhere.substring(0, strWhere.length()-1) + ")";
			sqlDel.append("delete from pc_material_cost where " + strWhere);
			log.info("sqlDel="+sqlDel);
			super.executeUpdate(sqlDel.toString(),false);
			
			sqlUpdate.append("update pc_material_cost set quantity = '0'");
			log.info("sqlUpdate="+sqlUpdate);
			super.executeUpdate(sqlUpdate.toString(),false);
			
			for (i = 1;i < sheet.getRows() - 1; i++) {
				code = sheet.getCell(1, i).getContents();
				qty = sheet.getCell(6, i).getContents();
				price = sheet.getCell(7, i).getContents();
				
				if(strCode.indexOf(",'"+code+"',")>-1){
					continue;
				}else if(Float.parseFloat(price)<0){
					price = String.valueOf(Math.abs(Float.parseFloat(price)));
					map = new HashMap();
					map.put("row", String.valueOf(i+1));
					map.put("code", code);
					map.put("name", sheet.getCell(2, i).getContents());
					list.add(map);
				}
				sql = new StringBuffer();
				sql.append("insert into pc_material_cost(material_code,unit_price,quantity) values (");
				sql.append("'" + code + "',substring(convert(varchar,round("+price+",2)),1,charindex('.',convert(varchar,round("+price+",2)))+2),'" + qty + "')");
//				log.info("sql="+sql);
				super.executeUpdate(sql.toString(),false);	
			}
//-------------------------------------------测试end------------------------------------------------------------------------------------
			
			if(map != null){
				actionForm.setErrorFlag("5");
				actionForm.setErrorList(list);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			actionForm.setUploadError(String.valueOf(i+1));
			actionForm.setErrorFlag("1");
			return false;
		}finally{
			book.close();
		}
		return true;
	}
	
	public void selectByCode(F0401ActionForm actionForm) throws SQLException{
		
		StringBuffer sql = new StringBuffer();
		ResultSet rest = null;
		
		sql.append("select m.code,m.name,c.unit_price,c.description,quantity qty from pc_material_cost c,t_materials m ");
		sql.append("where m.code = c.material_code and ");
		sql.append("	m.code = '"+actionForm.getMaterialCode()+"'");
		
		log.info(sql.toString());
		rest = super.executeQuery(sql.toString());
		while(rest.next()){
			actionForm.setMaterialCode(CommonTools.null2String(rest.getString("code")));
			actionForm.setMaterialName(CommonTools.null2String(rest.getString("name")));
			actionForm.setUnitPrice(CommonTools.convertCurrencyRMB(CommonTools.null2String(rest.getString("unit_price"))).substring(1));
			actionForm.setQty(CommonTools.null2String(rest.getString("qty")));
			actionForm.setDescription(CommonTools.null2String(rest.getString("description")));
		}	
	}
	
	public void update(F0401ActionForm actionForm) throws SQLException{
		
		StringBuffer sql = new StringBuffer();
		sql.append("update pc_material_cost set unit_price = "+actionForm.getUnitPrice());
		sql.append(",description = '"+CommonTools.null2String(actionForm.getDescription())+"'");
		sql.append(" where material_code = '"+CommonTools.null2String(actionForm.getMaterialCode())+"'");
		
		log.info(sql.toString());
		super.executeUpdate(sql.toString());
	}
	
	public void delete(F0401ActionForm actionForm) throws SQLException{
		
		StringBuffer sql = new StringBuffer();
		sql.append("delete from pc_material_cost ");
		sql.append(" where material_code = '"+CommonTools.null2String(actionForm.getId())+"'");
		log.info(sql.toString());
		
		super.executeUpdate(sql.toString());
	}
}
